"""
SqlAlchermy 三种排序方式详解
1. 模型定义中，添加__mapper_args__
2. relationship的order_by参数, 在指定relationship的时候，传递order_by参数来指定排序的字段
3. 正向排序和反向排序：默认情况是从小到大，从前到后排序的，如果想要反向排序，可以调用排序的字段的desc方法
"""

import os
import sys

path = os.path.dirname(__file__)
sys.path.append(os.path.join(path, '..'))

import random
from datetime import datetime
from faker import Faker
from sqlalchemy import Column, Integer, String, DECIMAL, DateTime, ForeignKey
from sqlalchemy.orm import relationship, backref
from conf.mysql_config import SESSION, BASE
from utils.random_time import gen_random_time


class CreditCardOrm(BASE):
    __tablename__ = 'credit_card'
    id = Column(Integer, autoincrement=True, primary_key=True)
    card_number = Column(String(100), comment='信用卡号', doc={'zh': '信用卡号'})
    card_provider = Column(String(100), comment='信用卡类型', doc={'zh': '信用卡类型'})
    card_security_code = Column(String(20), comment='信用卡安全码', doc={'zh': '信用卡安全码'})
    create_time = Column(DateTime, default=datetime.now, comment='创建日期', doc={'zh': '创建日期'})
    update_time = Column(DateTime, default=datetime.now, onupdate=datetime.now, comment='更新日期', doc={'zh': '更新日期'})

    def __str__(self):
        return f'<CreditCardOrm card_number={self.card_number}>'

    __repr__ = __str__


class CreditCardExtendOrm(BASE):
    __tablename__ = 'credit_card_extend'
    id = Column(Integer, autoincrement=True, primary_key=True)
    card_user_name = Column(String(100), comment='信用卡持有人姓名', doc={'zh': '信用卡持有人姓名'})
    card_money = Column(DECIMAL(9, 2), default=0.0, comment='余额', doc={'zh': '余额'})
    card_address = Column(String(100), comment='信用卡地址', doc={'zh': '信用卡地址'})
    create_time = Column(DateTime, default=datetime.now, comment='创建日期', doc={'zh': '创建日期'})
    update_time = Column(DateTime, default=datetime.now, onupdate=datetime.now, comment='更新日期', doc={'zh': '更新日期'})
    credit_card_id = Column(Integer, ForeignKey('credit_card.id'))
    # 设置uselist关键字参数为False，构建一对一关系
    credit_card = relationship('CreditCardOrm', backref=backref('extend', uselist=False))

    def __str__(self):
        return f'<CreditCardExtendOrm card_user_name={self.card_user_name}>'

    __repr__ = __str__


BASE.metadata.create_all()


def add_data():
    fake = Faker(locale='zh_CN')
    credit_cards = []
    for _ in range(50):
        try:
            card_number = fake.credit_card_number()
            gen_create_time = datetime.strptime(gen_random_time().get('date'), '%Y-%m-%d %H:%M:%S')
            credit_card = CreditCardOrm(card_number=card_number, card_provider=fake.credit_card_provider(),
                                        card_security_code=fake.credit_card_security_code(),
                                        create_time=gen_create_time)
            credit_card_extend = CreditCardExtendOrm(card_user_name=fake.name(),
                                                     card_money=random.randint(1000, 10000000),
                                                     card_address=fake.address(), create_time=gen_create_time)
            credit_card.extend = credit_card_extend
            credit_cards.append(credit_card)
        except (Exception,) as e:
            print(e)
    # 插入数据库
    SESSION.add_all(credit_cards)
    SESSION.commit()


def search_data():
    # 升序查询
    credit_cards = SESSION.query(CreditCardOrm.card_number,
                                 CreditCardOrm.create_time).order_by(CreditCardOrm.create_time).all()
    # 降序查询
    credit_cards = SESSION.query(CreditCardOrm.card_number,
                                 CreditCardOrm.create_time).order_by(CreditCardOrm.create_time.desc()).all()
    for card in credit_cards:
        print(card)


if __name__ == '__main__':
    search_data()
